package com.sena.crawler.run;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sena.crawler.util.JedisUtilNew;
import com.sena.crawler.util.ReadProperties;
import redis.clients.jedis.ShardedJedis;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class RunInsertSenaSpiderAttach {
    public static void main(String[] args) {
        JedisUtilNew jedisUtilNew = new JedisUtilNew(ReadProperties.getRedisUrlValue("url"));
        ShardedJedis client = jedisUtilNew.getInstance();
        Map<String, String> map = client.hgetAll("FILE_LINK");
        int count = 0;
        List<String> addList = new ArrayList<String>();
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");
        for (Map.Entry<String, String> entry : map.entrySet()) {
            String data = entry.getValue().replaceAll("\\\\", "");
            int i = data.lastIndexOf("{");
            int j = data.lastIndexOf("}");
            String sub = data.substring(i, j + 1);
            JSONObject urlVo = JSON.parseObject(sub);

            JSONArray arr = urlVo.getJSONArray("links");
            System.out.println(arr.toJSONString());
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
            String now = df.format(new Date());
            if (arr.size() > 0) {
                for (int k = 0; k < arr.size(); k++) {
                    String insertSql = "insert into sena_spider_attach(source_url,attach_url,status,create_time) values('" + entry.getKey() + "','" + arr.getString(k) + "'," + 0 + ",'" + now + "');";
                    addList.add(insertSql);
                }
            }
        }
        Connection conn = null;
        Statement stmt = null;

        count++;
        if (addList.size() >= 2000 || count == map.entrySet().size()) { //每1000条插入一次
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url, sql_user, sql_pwd);
                stmt = conn.createStatement();
                conn.setAutoCommit(false);
                stmt = conn.createStatement();

                for (int i = 0; i < addList.size(); i++) {
                    stmt.addBatch(addList.get(i).toString());
                }
                stmt.executeBatch();
                conn.commit();

                System.out.println("================成功插入附件链接" + count + "条");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                addList.clear();
                if (conn != null) {
                    try {
                        conn.close();
                        //System.out.println ("Database connection terminated");
                    } catch (Exception e) {
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                        //System.out.println ("Database connection terminated");
                    } catch (Exception e) {
                    }
                }
            }

        }
    }

    public static void insertSpiderAttachData() {
        JedisUtilNew jedisUtilNew = new JedisUtilNew(ReadProperties.getRedisUrlValue("url"));
        ShardedJedis client = jedisUtilNew.getInstance();
        Map<String, String> map = client.hgetAll("URL_FILE");

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String now = df.format(new Date());
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");

        int count = 0;
        List<String> addList = new ArrayList<String>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            JSONObject urlVo = JSON.parseObject(entry.getValue());
            String pUrl = "";
            if (urlVo.get("pUrl") != null && urlVo.get("pUrl").toString().indexOf("<") > 0) {  //都有尖括号的过滤
                int pos = urlVo.get("pUrl").toString().indexOf("<");
                pUrl = urlVo.get("pUrl").toString().substring(0, pos);
                // System.out.println("tempurl===="+tempurl);
            } else
                pUrl = urlVo.get("pUrl").toString();
            String insertSql = "insert into sena_spider_attach(source_url,attach_url,status,create_time) values('" + pUrl.replaceAll("'", "") + "','" + urlVo.getString("url").replaceAll("'", "") + "'," + 0 + ",'" + now + "');";
            //System.out.println(insertSql);
            Connection conn = null;
            Statement stmt = null;


            addList.add(insertSql);
            count++;
            if (addList.size() >= 2000 || count == map.entrySet().size()) { //每1000条插入一次
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    conn = DriverManager.getConnection(url, sql_user, sql_pwd);
                    stmt = conn.createStatement();
                    conn.setAutoCommit(false);
                    stmt = conn.createStatement();

                    for (int i = 0; i < addList.size(); i++) {
                        stmt.addBatch(addList.get(i).toString());
                    }
                    stmt.executeBatch();
                    conn.commit();

                    System.out.println("================成功插入附件链接" + count + "条");
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    addList.clear();
                    if (conn != null) {
                        try {
                            conn.close();
                            //System.out.println ("Database connection terminated");
                        } catch (Exception e) {
                        }
                    }
                    if (stmt != null) {
                        try {
                            stmt.close();
                            //System.out.println ("Database connection terminated");
                        } catch (Exception e) {
                        }
                    }
                }

            }
        }
    }

}
